using System;
using System.Collections.Generic;
using System.Text;
using Customs.Components.KDT.GC.Info;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Configuration;
namespace Customs.Components.KDT.GC
{
    public class DinhMuc: DinhMucInfo
    {
        public void Load(DinhMucInfo entity)
        {
            this._dinhmuc = entity.DinhMuc;
            this._Ghi_Chu = entity.Ghi_Chu;
            this._Ma_DVT = entity.DVT_ID;
            this._MaDoanhNghiep = entity.MaDoanhNghiep;
            this._MaHaiQuan = entity.MaHaiQuan;
            this._MaNPL = entity.MaNPL;
            this._MaSP = entity.MaSP;
            this._MuaNPL_VN = entity.MuaNPL_VN;
            this._NamTiepNhan = entity.NamTiepNhan;
            this._NgayKy = entity.NgayKy;
            this._NgayTiepNhan = entity.NgayTiepNhan;
            this._SoHopDong = entity.SoHopDong;
            this._SoTiepNhan = entity.SoTiepNhan;
            this._STTHang = entity.STTHang;
            this._TenNPL = entity.TenNPL;
            this._TenSP = entity.TenSP;
            this._TrangThaiXL = entity.TrangThaiXL;
            this._tyleHaoHut = entity.TyLeHaoHut;            
        }
        public bool Load()
        {
            SqlDataReader reader = this.Select();
            if (reader.Read())
            {
                if (!reader.IsDBNull(reader.GetOrdinal("SoTiepNhan"))) this.SoTiepNhan = reader.GetInt64(reader.GetOrdinal("SoTiepNhan"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) this.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("Nam_TN"))) this.NamTiepNhan = reader.GetInt16(reader.GetOrdinal("Nam_TN"));
                if (!reader.IsDBNull(reader.GetOrdinal("So_HD"))) this.SoHopDong = reader.GetString(reader.GetOrdinal("So_HD"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep")))this._MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ngay_Ky"))) this.NgayKy = reader.GetDateTime(reader.GetOrdinal("Ngay_Ky"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaSP"))) this.MaSP = reader.GetString(reader.GetOrdinal("MaSP"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaNPL"))) this.MaNPL = reader.GetString(reader.GetOrdinal("MaNPL"));
                if (!reader.IsDBNull(reader.GetOrdinal("DinhMuc"))) this.DinhMuc = reader.GetDecimal(reader.GetOrdinal("DinhMuc"));
                if (!reader.IsDBNull(reader.GetOrdinal("TyLeHH"))) this.TyLeHaoHut = reader.GetDecimal(reader.GetOrdinal("TyLeHH"));
                if (!reader.IsDBNull(reader.GetOrdinal("DVT_ID"))) this.DVT_ID = reader.GetString(reader.GetOrdinal("DVT_ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("TrangThaiXL"))) this.TrangThaiXL = reader.GetInt32(reader.GetOrdinal("TrangThaiXL"));
                if (!reader.IsDBNull(reader.GetOrdinal("NPL_Mua_VN"))) this.MuaNPL_VN = reader.GetDecimal(reader.GetOrdinal("NPL_Mua_VN"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ghi_Chu"))) this.Ghi_Chu = reader.GetString(reader.GetOrdinal("Ghi_Chu"));
                if (!reader.IsDBNull(reader.GetOrdinal("STTHang"))) this.STTHang = reader.GetInt32(reader.GetOrdinal("STTHang"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ten_SP"))) this.TenSP = reader.GetString(reader.GetOrdinal("Ten_SP"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ten_NPL"))) this.TenNPL = reader.GetString(reader.GetOrdinal("Ten_NPL"));
                
                reader.Close();

                return true;
            }
            reader.Close();
            return false;
        }
        private long getSoMax(SqlTransaction transaction)
        {
            string sqlSelect = "SELECT SoMax FROM [tblCAPSO_DMUC] WHERE ([Ma_HQTN] = @MaHaiQuan) AND ([Nam_TN] = @NamTiepNhan)";

            SqlParameter[] parameters = new SqlParameter[2];

            parameters[0] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = this._MaHaiQuan;

            parameters[1] = new SqlParameter("@NamTiepNhan", SqlDbType.SmallInt);
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = this._NamTiepNhan;

            SqlDataReader reader = SqlHelper.ExecuteReader(transaction, CommandType.Text, sqlSelect, parameters);
            long result = 0;
            if (reader.Read())
            {
                result = Int64.Parse(reader["SoMax"].ToString());
            }
            reader.Close();
            return result;
        }

        private int updateSoMax(SqlTransaction transaction)
        {
            string sqlUpdate = "UPDATE [tblCAPSO_DMUC] SET [SoMax] = [SoMax] + 1 WHERE ([Ma_HQTN] = @MaHaiQuan) AND ([Nam_TN] = @NamTiepNhan)";

            SqlParameter[] parameters = new SqlParameter[2];

            parameters[0] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = this._MaHaiQuan;

            parameters[1] = new SqlParameter("@NamTiepNhan", SqlDbType.SmallInt);
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = this._NamTiepNhan;

            return SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sqlUpdate, parameters);
        }

        private int insertSoMax(SqlTransaction transaction)
        {
            string sqlInsert = "INSERT INTO [tblCAPSO_DMUC] ([Ma_HQTN],	[Nam_TN], [SoMax]) VALUES (@MaHaiQuan, @NamTiepNhan, 1)";

            SqlParameter[] parameters = new SqlParameter[2];

            parameters[0] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = this._MaHaiQuan;

            parameters[1] = new SqlParameter("@NamTiepNhan", SqlDbType.SmallInt);
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = this._NamTiepNhan;

            return SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sqlInsert, parameters);
        }

        //-----------------------------------------------------------------------------------------
        public long GetSoTiepNhan(SqlTransaction transaction)
        {
            long somax = this.getSoMax(transaction);
            if (somax == 0)
            {
                this.insertSoMax(transaction);
                somax = 1;
            }
            else
            {
                this.updateSoMax(transaction);
                somax = somax + 1;
            }
            return somax;
        }

        public SqlDataReader Select()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ");
            query.Append("So_TNDKDT AS [SoTiepNhan], ");
            query.Append("Ma_HQTN AS [MaHaiQuan], ");
            query.Append("Nam_TN AS [Nam_TN], ");
            query.Append("So_HD AS [So_HD], ");
            query.Append("Ma_HQHD AS [Ma_HQHD], ");
            query.Append("DVGC AS [MaDoanhNghiep], ");
            query.Append("SPP_Code AS [MaSP], ");
            query.Append("Ngay_Ky AS [Ngay_Ky], ");
            query.Append("NPLP_Code AS [MaNPL], ");
            query.Append("Ten_SP AS [Ten_SP], ");
            query.Append("Ten_NPL AS [Ten_NPL], ");
            query.Append("DMGC AS [DinhMuc], ");
            query.Append("TLHH AS [TyLeHH], ");
            query.Append("Ma_DVT AS [DVT_ID], ");
            query.Append("TrangThaiXL AS [TrangThaiXL], ");
            query.Append("Mua_VN AS [NPL_Mua_VN], ");
            query.Append("Ghi_Chu AS [Ghi_Chu], ");
            query.Append("STTHang AS [STTHang] ");
            query.Append("FROM dbo.[tblDDMUC] ");
            query.Append("WHERE ");
            query.Append("So_TNDKDT = @So_TNDKDT AND Ma_HQTN = @MaHaiQuan AND Nam_TN = @Nam_TN");

            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@So_TNDKDT", SqlDbType.BigInt);
            parameters[0].Value = this._SoTiepNhan;

            parameters[1] = new SqlParameter("@MaHaiQuan", SqlDbType.Char,6);
            parameters[1].Value = this.MaHaiQuan;

            parameters[2] = new SqlParameter("@Nam_TN", SqlDbType.SmallInt);
            parameters[2].Value = this._NamTiepNhan;
         
            return SqlHelper.ExecuteReader(connectionString, CommandType.Text, query.ToString(), parameters);
        }
        public int Delete(SqlTransaction transaction)
        {
            string query = string.Format("DELETE FROM tblDDMUC WHERE So_TNDKDT = {0} AND Ma_HQTN = '{1}' AND Nam_TN = {2} ", this.SoTiepNhan, this.MaHaiQuan, this.NamTiepNhan);
            return SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, query);
        }
       
        //---------------------------------------------------------------------------------------------
  
        public bool HuyDinhMuc()
        {
            bool returnValue;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString))
            {
                conn.Open();
                SqlTransaction transaction = conn.BeginTransaction();

                try
                {
                    this.Delete(transaction);
                    transaction.Commit();
                    returnValue = true;
                }
                catch
                {
                    returnValue = false;
                    transaction.Rollback();
                }
                finally
                {
                    conn.Close();
                }
            }
            return returnValue;
        }



        public long KhaiBaoDinhMuc(DinhMucInfoCollection collection)
        {            
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString))
            {
                conn.Open();
                SqlTransaction transaction = conn.BeginTransaction();

                try
                {                    
                    this.Load(collection[0]);
                    if (this.SoTiepNhan > 0)
                    {
                        this.Load();
                        if (this.TrangThaiXL == 1)
                            throw new Exception("1");
                        this.Delete(transaction);
                        this.Load(collection[0]);
                    }
                    this.NamTiepNhan = DateTime.Today.Year;
                    this.SoTiepNhan = GetSoTiepNhan(transaction);
                   
                    foreach (DinhMucInfo itemInfo in collection)
                    {
                        DinhMuc dm = new DinhMuc();
                        dm.Load(itemInfo);
                        dm.SoTiepNhan = this.SoTiepNhan;
                        dm.NamTiepNhan=this.NamTiepNhan;
                        dm.InsertTransaction(transaction);
                    }
                    transaction.Commit();                   
                }
                catch(Exception ex)
                {                    
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
            return this.SoTiepNhan;
            
        }

        //---------------------------------------------------------------------------------------------
        public void InsertTransaction(SqlTransaction transaction)
        {
            StringBuilder query = new StringBuilder();
            query.Append("INSERT INTO tblDDMUC ( ");
            query.Append("[So_TNDKDT] , ");
            query.Append("[Ma_HQTN] , ");
            query.Append("[Nam_TN] , ");
            query.Append("[So_HD] , ");
            query.Append("[Ma_HQHD] , ");
            query.Append("[Ngay_Ky] , ");
            query.Append("[DVGC] , ");
            query.Append("[SPP_Code] , ");
            query.Append("[NPLP_Code] , ");
            query.Append("[Ten_SP] , ");
            query.Append("[Ten_NPL] , ");
            query.Append("[DMGC] , ");
            query.Append("[TLHH] , ");
            query.Append("[Ma_DVT] , ");
            query.Append("[TrangThaiXL] , ");
            query.Append("[KieuKB] , ");
            query.Append("[Ngay_VM] , ");
            query.Append("[Mua_VN] , ");

            query.Append("[Ghi_Chu] , ");
            query.Append("[STTHang] , ");
            query.Append("[Ma_DVKB] ) ");
          
            query.Append("VALUES ( ");
            query.Append("@So_TNDKDT , ");
            query.Append("@Ma_HQTN , ");
            query.Append("@Nam_TN , ");
            query.Append("@So_HD , ");
            query.Append("@Ma_HQHD , ");
            query.Append("@Ngay_Ky , ");
            query.Append("@DVGC , ");
            query.Append("@MaSP , ");
            query.Append("@MaNPL , ");
            query.Append("@Ten_SP , ");
            query.Append("@Ten_NPL , ");
            query.Append("@DMGC , ");
            query.Append("@TLHH , ");
            query.Append("@Ma_DVT , ");
            query.Append("@TrangThaiXL , ");
            query.Append("@KieuKB , ");
            query.Append("@Ngay_VM , ");
            query.Append("@NPL_Mua_VN , ");
            query.Append("@Ghi_Chu , ");
            query.Append("@STTHang , ");
            query.Append("@Ma_DVKB ) ");                     

            SqlParameter[] parameters = new SqlParameter[21];

            parameters[0] = new SqlParameter("@So_TNDKDT", SqlDbType.BigInt);
            parameters[0].Value = this.SoTiepNhan;

            parameters[1] = new SqlParameter("@Ma_HQTN", SqlDbType.Char, 6);
            parameters[1].Value = this.MaHaiQuan;

            parameters[2] = new SqlParameter("@Nam_TN", SqlDbType.SmallInt);
            parameters[2].Value = this.NamTiepNhan;

            parameters[3] = new SqlParameter("@So_HD", SqlDbType.VarChar);
            parameters[3].Value = this.SoHopDong;

            parameters[4] = new SqlParameter("@Ma_HQHD", SqlDbType.VarChar);
            parameters[4].Value = this.MaHaiQuan;

            parameters[5] = new SqlParameter("@Ngay_Ky", SqlDbType.DateTime);
            parameters[5].Value = this.NgayKy;

            parameters[6] = new SqlParameter("@DVGC", SqlDbType.VarChar);
            parameters[6].Value = this.MaDoanhNghiep;

            parameters[7] = new SqlParameter("@MaSP", SqlDbType.VarChar);
            parameters[7].Value = this._MaSP;


            parameters[8] = new SqlParameter("@MaNPL", SqlDbType.VarChar);
            parameters[8].Value = this._MaNPL;

            parameters[9] = new SqlParameter("@Ten_SP", SqlDbType.NVarChar);
            parameters[9].Value = this._TenSP;

            parameters[10] = new SqlParameter("@Ten_NPL", SqlDbType.NVarChar);
            parameters[10].Value = this._TenNPL;

            parameters[11] = new SqlParameter("@DMGC", SqlDbType.Decimal);
            parameters[11].Value = this._dinhmuc;

            parameters[12] = new SqlParameter("@TLHH", SqlDbType.Decimal);
            parameters[12].Value = this._tyleHaoHut;

            parameters[13] = new SqlParameter("@Ma_DVT", SqlDbType.Char, 3);
            parameters[13].Value = this._Ma_DVT;

            parameters[14] = new SqlParameter("@TrangThaiXL", SqlDbType.Int);
            parameters[14].Value = 0;

            parameters[15] = new SqlParameter("@KieuKB", SqlDbType.Int);
            parameters[15].Value = 1;

            parameters[16] = new SqlParameter("@Ngay_VM", SqlDbType.DateTime);
            parameters[16].Value = DateTime.Today;

            parameters[17] = new SqlParameter("@NPL_Mua_VN", SqlDbType.Decimal);
            parameters[17].Value = this._MuaNPL_VN;

            parameters[18] = new SqlParameter("@Ghi_Chu", SqlDbType.NVarChar);
            parameters[18].Value = this._Ghi_Chu;

            parameters[19] = new SqlParameter("@STTHang", SqlDbType.Int);
            parameters[19].Value = this._STTHang;

            parameters[20] = new SqlParameter("@Ma_DVKB", SqlDbType.Char,14);
            parameters[20].Value = this._MaDoanhNghiep;

            SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, query.ToString(), parameters);
        }
        public static DinhMucInfoCollection RequestStatus(string mahaiquan,int namtiepnhan,long sotiepnhan)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ");
            query.Append("So_TNDKDT AS [SoTiepNhan], ");
            query.Append("Ma_HQTN AS [MaHaiQuan], ");
            query.Append("Nam_TN AS [Nam_TN], ");
            query.Append("So_HD AS [So_HD], ");
            query.Append("Ma_HQHD AS [Ma_HQHD], ");
            query.Append("DVGC AS [MaDoanhNghiep], ");
            query.Append("SPP_Code AS [MaSP], ");
            query.Append("Ngay_Ky AS [Ngay_Ky], ");
            query.Append("NPLP_Code AS [MaNPL], ");
            query.Append("Ten_SP AS [Ten_SP], ");
            query.Append("Ten_NPL AS [Ten_NPL], ");
            query.Append("DMGC AS [DinhMuc], ");
            query.Append("TLHH AS [TyLeHH], ");
            query.Append("Ma_DVT AS [DVT_ID], ");
            query.Append("TrangThaiXL AS [TrangThaiXL], ");
            query.Append("Mua_VN AS [NPL_Mua_VN], ");
            query.Append("Ghi_Chu AS [Ghi_Chu], ");
            query.Append("STTHang AS [STTHang] ");
            query.Append("FROM dbo.[tblDDMUC] ");
            query.Append("WHERE ");
            query.Append("So_TNDKDT = @So_TNDKDT AND Ma_HQTN = @MaHaiQuan AND Nam_TN = @Nam_TN");

            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@So_TNDKDT", SqlDbType.BigInt);
            parameters[0].Value = sotiepnhan;

            parameters[1] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
            parameters[1].Value = mahaiquan;

            parameters[2] = new SqlParameter("@Nam_TN", SqlDbType.SmallInt);
            parameters[2].Value = namtiepnhan;

            SqlDataReader reader = SqlHelper.ExecuteReader(connectionString, CommandType.Text, query.ToString(), parameters);
            DinhMucInfoCollection collection = new DinhMucInfoCollection();
            while (reader.Read())
            {
                DinhMucInfo dmInfo = new DinhMucInfo();
                if (!reader.IsDBNull(reader.GetOrdinal("SoTiepNhan"))) dmInfo.SoTiepNhan = reader.GetInt64(reader.GetOrdinal("SoTiepNhan"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) dmInfo.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("Nam_TN"))) dmInfo.NamTiepNhan = reader.GetInt16(reader.GetOrdinal("Nam_TN"));
                if (!reader.IsDBNull(reader.GetOrdinal("So_HD"))) dmInfo.SoHopDong = reader.GetString(reader.GetOrdinal("So_HD"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) dmInfo.MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ngay_Ky"))) dmInfo.NgayKy = reader.GetDateTime(reader.GetOrdinal("Ngay_Ky"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaSP"))) dmInfo.MaSP = reader.GetString(reader.GetOrdinal("MaSP"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaNPL"))) dmInfo.MaNPL = reader.GetString(reader.GetOrdinal("MaNPL"));
                if (!reader.IsDBNull(reader.GetOrdinal("DinhMuc"))) dmInfo.DinhMuc = reader.GetDecimal(reader.GetOrdinal("DinhMuc"));
                if (!reader.IsDBNull(reader.GetOrdinal("TyLeHH"))) dmInfo.TyLeHaoHut = reader.GetDecimal(reader.GetOrdinal("TyLeHH"));
                if (!reader.IsDBNull(reader.GetOrdinal("DVT_ID"))) dmInfo.DVT_ID = reader.GetString(reader.GetOrdinal("DVT_ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("TrangThaiXL"))) dmInfo.TrangThaiXL = reader.GetInt32(reader.GetOrdinal("TrangThaiXL"));
                if (!reader.IsDBNull(reader.GetOrdinal("NPL_Mua_VN"))) dmInfo.MuaNPL_VN = reader.GetDecimal(reader.GetOrdinal("NPL_Mua_VN"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ghi_Chu"))) dmInfo.Ghi_Chu = reader.GetString(reader.GetOrdinal("Ghi_Chu"));
                if (!reader.IsDBNull(reader.GetOrdinal("STTHang"))) dmInfo.STTHang = reader.GetInt32(reader.GetOrdinal("STTHang"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ten_SP"))) dmInfo.TenSP = reader.GetString(reader.GetOrdinal("Ten_SP"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ten_NPL"))) dmInfo.TenNPL = reader.GetString(reader.GetOrdinal("Ten_NPL"));
             
                collection.Add(dmInfo);
            }
            reader.Close();
            return collection;
        }
    }
}
